<?php

namespace App\Command\Changedata;

use Dotenv\Validator;
use EchoPHP\Console\Command;
use EchoPHP\Facades\Db;
use EchoPHP\Facades\Log;

class InitProjectDefaultSetting extends Command {
    protected $signature = "changedata:initprojectdefaultsetting";
    protected $description = '初始化项目下沟通表头和交付方式';


    // 校验项目沟通表头设置，线上初始化执行后，所有项目22个表头，总数目与总项目一致
    //      select count(1) as count from Project;
    //      select projectid,count(1) as count from ContactField WHERE `status`='01' GROUP BY projectid;
    // 校验职位沟通记录数据, 线上初始化执行后，所有职位22个表头，总数目与总职位数一致
    //      select hascontact,count(1) as count from SCase group by hascontact;
    //      select caseid,count(1) as count from CaseContactField GROUP BY caseid;

    public function handle() {
        $this->initProjectContactField();
        $this->initCaseContactField();

        $this->initProjectChargeWay();
        $this->initCaseChargeWay();
    }

    /**
     * 初始化项目默认沟通表头
     * @return int
     * @throws \Exception
     */
    public function initProjectContactField() {
        $defaultContactFieldList = include 'defaultcontactfield.php';    //默认添加全部系统字段
        //获取没有定义沟通表头的项目id
        $sql = 'select projectid,subid from Project where projectid not in (select projectid from ContactField where status=? group by projectid)';
        $var = ['01'];
        $result = Db::select($sql, $var);
        if (empty($result)) {
            $this->output->title('所有项目均已定义沟通表头');
            return 0;
        }

        foreach ($result as $projectInfo) {
            $insertSql = 'insert into ContactField (fieldname,showname,unit,type,arguments,creater,creatername,updater,updatername,projectid,status,subid,issys,createdate,updatedate) values ';
            $insertVar = $values = [];
            foreach ($defaultContactFieldList as $item) {
                $values[] = '(?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $var = [
                    $item['fieldname'], $item['showname'], $item['unit'], $item['type'],
                    json_encode($item['arguments']), 1, '超级管理员', 1, '超级管理员',
                    $projectInfo['projectid'], '01', $projectInfo['subid'], $item['issys']
                ];
                $insertVar = array_merge($var, $insertVar);
            }
            $insertSql .= implode(',', $values);
            $result = Db::insert($insertSql, $insertVar);
            if (!$result) {
                $this->output->error('projectid:' . $projectInfo['projectid'] . '添加沟通表头失败');
                Log::error('添加沟通表头失败 ', [$insertSql, $insertVar]);
            }
        }
        $this->output->success('项目沟通表头设置完成');
        return 1;
    }

    /**
     * 初始化项目默认交付方式
     * @return int
     * @throws \Throwable
     */
    public function initProjectChargeWay() {
        $defaultChargeWay = [   //默认交付方式 事务性工作，目标0 节点绑定简历推荐-已推荐 固定金额 100元 不限
            'chargeway' => 1,
            'targetnum' => 0,
            'bindflow' => '0302',
            'chargetype' => 3,
            'standard' => 100,
            'salarytype' => 1,
            'salaryfrom' => 0,
            'salaryto' => 0
        ];
        //获取没有定义交付方式的项目id
        $sql = 'select projectid,subid from Project where projectid not in (select projectid from ChargeWay where status=? group by projectid)';
        $var = ['01'];
        $result = Db::select($sql, $var);
        if (empty($result)) {
            $this->output->title('所有项目均已定义交付方式');
            return 0;
        }
        foreach ($result as $projectInfo) {
            Db::connect('jyrpo_pdo')->transaction(function () use ($defaultChargeWay, $projectInfo) {
                $insertSql = 'insert into ChargeWay (projectid,subid,chargeway,targetnum,bindflow,status,
                creater,creatername,updater,updatername,createdate,updatedate) values (?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $insertVar = [
                    $projectInfo['projectid'],
                    $projectInfo['subid'],
                    $defaultChargeWay['chargeway'],
                    $defaultChargeWay['targetnum'],
                    $defaultChargeWay['bindflow'],
                    '01',
                    1,
                    '超级管理员',
                    1,
                    '超级管理员'
                ];
                $result = Db::connect('jyrpo_pdo')->insert($insertSql, $insertVar);
                if (!$result) {
                    $this->output->error('projectid:' . $projectInfo['projectid'] . '新增交付方式失败');
                    return 0;
                }
                $chargeId = Db::connect('jyrpo_pdo')->getLastInsertId();
                $insertSql2 = 'insert into ChargeStandard (projectid,subid,chargeid,chargetype,standard,salarytype,salaryfrom,salaryto,status,
                            creater,creatername,updater,updatername,createdate,updatedate) values (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $insertVar2 = [
                    $projectInfo['projectid'],
                    $projectInfo['subid'],
                    $chargeId,
                    $defaultChargeWay['chargetype'],
                    $defaultChargeWay['standard'],
                    $defaultChargeWay['salarytype'],
                    $defaultChargeWay['salaryfrom'],
                    $defaultChargeWay['salaryto'],
                    '01',
                    1,
                    '超级管理员',
                    1,
                    '超级管理员'
                ];
                $result = Db::connect('jyrpo_pdo')->insert($insertSql2, $insertVar2);
                if (!$result) {
                    $this->output->error('projectid:' . $projectInfo['projectid'] . '新增收费标准失败');
                    return 0;
                }
            });
        }
        $this->output->success('项目交付方式设置完成');
        return 1;
    }

    /**
     * 初始化职位默认沟通表头
     * @return int
     */
    public function initCaseContactField() {
        //找出所有未设置沟通表头的职位
        $sql = 'select projectid,caseid from SCase where hascontact=?';
        $var = [0];
        $caseInfos = Db::select($sql, $var);
        if (empty($caseInfos)) {
            $this->output->title('所有职位均已设置沟通表头');
            return 0;
        }

        //项目下的所有沟通表头
        $sql = 'select projectid,GROUP_CONCAT(id) as fieldidlist from ContactField where status=? and projectid in (select projectid from SCase where hascontact=?) GROUP BY projectid';
        $var = ['01', 0];
        $contactFields = Db::select($sql, $var);

        if (empty($contactFields)) {
            $this->output->title('查询职位关联项目沟通表头为空');
            return 0;
        }

        $contactFieldList = [];
        foreach ($contactFields as $contactField) {
            $contactFieldList[$contactField['projectid']] = $contactField['fieldidlist'];
        }

        foreach ($caseInfos as $caseInfo) {
            $insertSql = 'insert into CaseContactField (caseid,fieldidlist,creater,creatername,updater,updatername,status,createdate,updatedate) values (?,?,?,?,?,?,?,Now(),Now())';
            $insertVar = [
                $caseInfo['caseid'], $contactFieldList[$caseInfo['projectid']], 1, '超级管理员', 1, '超级管理员', '01',
            ];
            $statement1 = Db::connect()->prepareStatement($insertSql, $insertVar);

            $updateSql = 'update SCase set hascontact=? where caseid=?';
            $updateVar = [1, $caseInfo['caseid']];
            $statement2 = Db::connect()->prepareStatement($updateSql, $updateVar);

            $result = Db::connect()->prepareTransactionStatement(false, $statement1, $statement2)->execute();
            if (!$result) {
                $this->output->error('caseid:' . $caseInfo['caseid'] . '添加沟通表头失败');
                Log::error('职位添加沟通表头失败 ', [$insertSql, $insertVar]);
            }
        }
        $this->output->success('职位沟通表头设置完成');
        return 1;
    }

    /**
     * 初始化职位设置默认交付方式
     * @return int
     */
    public function initCaseChargeWay() {
        //找出所有未设置交付方式的职位
        $sql = 'select projectid,caseid from SCase where haschargeway=?';
        $var = [0];
        $result = Db::select($sql, $var);
        if (empty($result)) {
            $this->output->title('所有职位均已设置交付方式');
            return 0;
        }
        //获取所有项目下第一个（按照id排序）交付方式
        $sql = 'select projectid,id,chargeid from ChargeStandard where status=? and projectid in(select projectid from SCase where haschargeway=?) group by projectid';
        $var = ['01', 0];
        $chargeStandards = Db::select($sql, $var);
        $chargeStandards = array_column($chargeStandards, null, 'projectid');

        foreach ($result as $caseInfo) {
            $insertSql = 'insert into CaseChargeWay (caseid,chargewayid,chargestandardid,creater,creatername,updater,updatername,status,createdate,updatedate)
                values (?,?,?,?,?,?,?,?,Now(),Now())';
            $insertVar = [
                $caseInfo['caseid'],
                $chargeStandards[$caseInfo['projectid']]['chargeid'],
                $chargeStandards[$caseInfo['projectid']]['id'],
                1,
                '超级管理员',
                1,
                '超级管理员',
                '01',
            ];
            $statement1 = Db::connect()->prepareStatement($insertSql, $insertVar);

            $updateSql = 'update SCase set haschargeway=? where caseid=?';
            $updateVar = [1, $caseInfo['caseid']];
            $statement2 = Db::connect()->prepareStatement($updateSql, $updateVar);

            $result = Db::connect()->prepareTransactionStatement(true, $statement1, $statement2)->execute();
            if (!$result) {
                $this->output->error('caseid:' . $caseInfo['caseid'] . '添加交付方式失败');
                Log::error('职位添加交付方式失败 ', [$insertSql, $insertVar]);
            }
        }
        $this->output->success('职位交付方式设置完成');
        return 1;
    }
}
